from generate_common import handleValueApply, handleCustomApply

def genItem(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    # 获得operator，值要么就是 with，要么就是 without
    operator = filterDatum['operator']
    # 针对 operator，制作 operSql 和 operContraSql
    # operSql = "" if operator == 'with' else "NOT"，分别对应保留与删除两种操作
    operKeepSql = "" if operator == 'with' else "NOT"
    operDropSql = "" if operator == 'without' else "NOT"

    # 获得 inOne，默认值为 "case"
    inOne = filterDatum.get('in_one_', 'case')

    # 获得value_apply，处理产生 valueCodesSql 和 valueNamesSql
    # valueCodesSql, valueNamesSql = handleValueApply(filterDatum, type='item', tblAbbr='d2')
    # 从 ruleAct 中获得triggerApply
    triggerApply = ruleAct['trigger_apply']
    triggerCodes = [v['j_code'] for v in triggerApply]
    # 从 filterDatum 中获得 value_apply
    valueObjApply = filterDatum['value_apply']
    # 要对 valueObjApply 进行处理，剔除里面 triggerApply 中同一个 item_j_code 的内容，避免重复误审全报
    valueObjApply = [v for v in valueObjApply if v['j_code'] not in triggerCodes]
    # 获得 valueNames
    valueNames = filterDatum['value']
    # 处理 valueCodesSql 和 valueNamesSql
    valueCodesSql, valueNamesSql = handleCustomApply(valueObjApply, valueNames, type='item', tblAbbr='d')
    
    # 如果没有 valueCodesSql 
    if not valueCodesSql:
        # 如果 operator 是 with，则说明要维持违规必须要同时存在项目，没有出现，删掉所有记录并且输出
        if operator == 'with':
            return [
                f"""
                    -- item with but no item matched，直接删除所有记录，因为要维持违规必须要同时存在项目，没有出现
                    DELETE FROM vlt_tmp_a
                """
            ]
        # 如果 operator 是 without，则说明要维持违规必须要排除的项目，没有出现，则保留所有记录并且输出
        elif operator == 'without':
            return [
                f"""
                    -- item without but no item matched， 直接保留所有记录，要维持违规必须要排除的项目，没有出现
                    DELETE FROM vlt_tmp_a WHERE 1=0
                """
            ]
        else:
            raise ValueError(f"在 item，Invalid operator value: {operator}")        

    # 针对 price_lt_ 条件，制作条件的sql语句
    priceLt = filterDatum.get('price_lt_', None)
    priceLtSql = ""
    if priceLt is not None:
        priceLtSql = f"AND d.p < {priceLt}\n"
    
    # 针对 is_material 条件，制作条件的sql语句
    isMaterial = filterDatum.get('is_material', None)
    isMaterialSql = ""
    if isMaterial is not None:
        isMaterialSql = f"AND d.item_type = '卫生材料费'\n"
    
    # 针对 time_gte, time_lte, time_unit 条件，制作条件的sql语句
    timeGte = filterDatum.get('time_gte', None)
    timeLte = filterDatum.get('time_lte', None)
    timeUnit = filterDatum.get('time_unit', 'minute')
    timeSql = ""
    if timeGte is not None:
        timeSql += f"AND d.item_time >= va.item_time + NUMTODSINTERVAL('{timeGte}', '{timeUnit.upper()}')\n"
    if timeLte is not None:
        timeSql += f"AND d.item_time <= va.item_time + NUMTODSINTERVAL('{timeLte}', '{timeUnit.upper()}')\n"
    
    # 针对 in_same_dept, in_diff_dept，如果有且值为 True，则制作条件的sql语句
    inSameDept = filterDatum.get('in_same_dept', False)
    inDiffDept = filterDatum.get('in_diff_dept', False)
    deptSql = ""
    if inSameDept:
        deptSql = "AND va.item_dept = d.item_dept\n"
    if inDiffDept:
        deptSql = "AND va.item_dept <> d.item_dept\n"

    # 构建 SQL 语句，除非 valueCodes 和 valueNames 都为空，则不需要查询 item 表
    if not valueCodesSql and not valueNamesSql:
        return []
    
    """
    ============ 思路 ============
    1. 查出 va 表中符合违规范围的 ，放在 vb 表
    2. 删除 va 表中没有 vb 的部分
    3. 将 vb 表的关联 item 信息更新到 va 表（仅当 operator=with 的时候）
    item_j_code、item_j_name、item_code、item_hsp_code、item_hsp_name、item_dept、apply_dept、item_time（作为关联 item 信息）
    q、p、c、b（作为关联 item 信息）
    """
    
    # 根据 inOne 构建 joinKeys
    if inOne == 'case':
        joinKeys = ['setl_id']
    elif inOne == 'day':
        joinKeys = ['setl_id', 'item_day']
    elif inOne == 'hour':
        joinKeys = ['setl_id', 'item_day', 'item_hour']
    elif inOne == 'year':
        joinKeys = ['psn_no', 'j_year']
    elif inOne == 'patient':
        joinKeys = ['psn_no']
    else:
        # 不合法的 inOne 值，抛出异常
        raise ValueError(f"在 item，Invalid inOne value: {inOne}")
    
    # 通过 joinKeys，构建 selectSql 与 joinSql
    insertSql1 = ', '.join(joinKeys)
    selectSql1 = ', '.join([f"va.{k}" for k in joinKeys])
    joinSql1 = ' AND '.join([f"va.{k} = d.{k}" for k in joinKeys])
    joinSql2 = ' AND '.join([f"vb.{k} = va.{k}" for k in joinKeys])
    joinSql3 = ' AND '.join([f"va.{k} = vb2.{k}" for k in joinKeys])
    joinSql_vb_d = ' AND '.join([f"vb.{k} = d.{k}" for k in joinKeys])

    # 清空临时表
    sqlList = ["TRUNCATE TABLE vlt_tmp_b", "TRUNCATE TABLE vlt_tmp_b2"]
    
    # step1  查出 va 表中符合违规范围的（即有或者没有对应项目的），将违规项目保留 ，放在 vb 表
    sqlList.append(f"""
        -- item，step1. 查出 va 表中符合违规范围的（即有或者没有对应项目的） ，放在 vb 表
        INSERT into vlt_tmp_b (
            {insertSql1}, d0_rowid
        )
        SELECT
            {selectSql1}, d0_rowid
        FROM vlt_tmp_a va
        WHERE {operKeepSql} EXISTS(
            select 1
            from d
            where va.j_isin = d.j_isin and va.j_year = d.j_year and va.hsp_abbr = d.hsp_abbr
                and {joinSql1}
                {valueCodesSql} {valueNamesSql} {priceLtSql} {isMaterialSql} {timeSql} {deptSql}
        )
    """)

    
    # step2  删除 va 表中没有 vb 的部分
    sqlList.append(f"""
        -- item，stepp2. 删除 va 表中没有 vb 的部分
        DELETE FROM vlt_tmp_a va
        WHERE NOT EXISTS(
            SELECT 1
            FROM vlt_tmp_b vb
            WHERE va.d0_rowid = vb.d0_rowid
        )
    """)
        
    # step3  从d表中查出关联信息，放到 vb2 表（仅当 operator=with 的时候）；
    # step4  更新到 va 表（仅当 operator=with 的时候）
    if operator == 'with':
        # step3
        sqlList.append(f"""
            -- item，step3. 从d表中查出关联信息，放到 vb2 表（仅当 operator=with 的时候）
            INSERT INTO vlt_tmp_b2 (
                {insertSql1},
                item_j_code2, item_j_name2, item_code2, item_name2, item_hsp_code2, item_hsp_name2, item_dept2, apply_dept2, item_time2,
                q2, p2, c2, b2
            )
            SELECT
                {selectSql1},
                listagg(distinct d.item_j_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_j_code2,
                listagg(distinct d.item_j_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_j_name2,
                listagg(distinct d.item_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_code2,
                listagg(distinct d.item_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_name2,
                listagg(distinct d.item_hsp_code, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_hsp_code2,
                listagg(distinct d.item_hsp_name, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_hsp_name2,
                listagg(distinct d.item_dept, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS item_dept2,
                listagg(distinct d.apply_dept, ',') WITHIN GROUP (ORDER BY d.item_j_code) AS apply_dept2,
                TO_CHAR(MIN(d.item_time), 'yy-mm-dd hh24:mi:ss') || ' ~ ' || TO_CHAR(MAX(d.item_time), 'yy-mm-dd hh24:mi:ss') AS item_time2,
                SUM(d.q) AS q2,
                listagg(distinct ROUND(d.p,2), ',') WITHIN GROUP (ORDER BY d.item_j_code) AS p2,
                SUM(d.c) AS c2,
                SUM(d.b) AS b2
            FROM vlt_tmp_a va, d
            WHERE {joinSql1} {timeSql}
            {valueCodesSql} {valueNamesSql} {priceLtSql} {isMaterialSql} {timeSql} {deptSql}
            GROUP BY {selectSql1} 
            HAVING SUM(d.q) > 0
        """)
        
        # step4
        sqlList.append(f"""
            -- item，step4. 更新到 va 表（仅当 operator=with 的时候）
            MERGE INTO vlt_tmp_a va
            USING vlt_tmp_b2 vb2
            ON ({joinSql3})
            WHEN MATCHED THEN
            UPDATE SET 
                va.item_j_code2 = vb2.item_j_code2,
                va.item_j_name2 = vb2.item_j_name2,
                va.item_code2 = vb2.item_code2,
                va.item_hsp_code2 = vb2.item_hsp_code2,
                va.item_hsp_name2 = vb2.item_hsp_name2,
                va.item_dept2 = vb2.item_dept2,
                va.apply_dept2 = vb2.apply_dept2,
                va.item_time2 = vb2.item_time2,
                va.q2 = vb2.q2,
                va.p2 = vb2.p2,
                va.c2 = vb2.c2,
                va.b2 = vb2.b2
        """)
    
    return sqlList

